Sales Performance & Customer Behavior Analysis

Author

Tristyana Putri Andhiny

1. Introduction

Business Problem & Relevance

Understanding sales performance is crucial for business decision-making. This report analyzes sales data from Red30 Tech to identify key trends in sales performance, pricing impact, and customer purchasing behavior. The findings will support business strategy improvements.

About Red30 Tech

Red30 Tech is a leading provider of secure, customizable technology and business solutions designed for companies seeking scalable and sustainable results. With a focus on the ever-evolving technology landscape, the company aims to be the go-to provider for business technology solutions.

Currently, Red30 Tech serves over three billion people annually through its business applications, point-of-service contact solutions, and data storage. The company has 5,000 employees in 22 countries, with a goal of expanding to 100 countries by 2030, ensuring global entrepreneurs have access to environmentally sustainable technology solutions.

Customer Segments

Red30 Tech provides tailored technology solutions for businesses of all sizes, with a particular focus on: - Startups – Supporting emerging companies with scalable technology models. - Mid-sized enterprises – Core customers shaping the company’s research and product development. - Enterprise clients – Partnering with large corporations to develop cutting-edge business technology solutions.

Red30 Tech is fictious company solely made for the creation and development of training materials. For more information, visit the Red30 Tech Website.


2. Methodology

Libraries Used

Below are the R libraries used in this report and their purposes:

  • tidyverse – A collection of R packages for data manipulation, visualization, and analysis.
  • readxl – Reads Excel files into R for further processing.
  • dplyr – Part of tidyverse, used for data manipulation and filtering operations.
  • ggplot2 – Creates advanced visualizations and plots for data insights.
  • knitr – Formats tables and outputs neatly for reporting.
  • DBI – Provides an interface to work with databases, in this case, SQLite.
  • RSQLite – Enables interaction with SQLite databases from R.
  • Scales – Used for formatting numerical values in charts (e.g., adding commas for readability).

Data Acquisition & Preparation

Data Loading

The data for this analysis comes from Red30 Tech Sales stored in an Excel file. To ensure efficient handling, the dataset is loaded into a SQLite database and ensure the data has been loaded properly.

[1] "sales"
      EmpID      Employee Name          Employee Job Title   Sales Region
1 900016297     Joseph Drydale          Sales Associate II S Central East
2 900013626     Michele Cornes Senior Sales Representative   Central East
3 900018190 Anthony Smallpeace Senior Sales Representative   Central East
4 900014032     Bert Picheford          Sales Associate II   Central East
5 900013387        Monty Urwen         Sales Associate III      Southeast
  OrderNum  OrderDate OrderType CustomerType               CustName
1  1100938 1504224000    Retail     Business Wilderman Technologies
2  1100934 1504224000 Wholesale     Business       Gusikowski Group
3  1100935 1504224000    Retail   Individual      Spencer Educators
4  1100937 1504224000    Retail   Individual           Saxon Laviss
5  1100936 1504224000 Wholesale     Business          Schinner Inc.
       CustState    ProdCategory ProdNumber                  ProdName Quantity
1          Texas          eBooks      EB502 Building Your First Robot        4
2 North Carolina      Blueprints      BP102   Bsquare Robot Blueprint       10
3       Delaware      Drone Kits      DK204                  BYOD-300        2
4       Virginia      Robot Kits      RK602                 BYOR-1000        1
5        Florida Training Videos      TV801           Aerial Security       10
   Price Discount Order Total Payment Plan Payment Status DateCustAdded
1  24.95    0.000      99.800          Yes           Paid          2014
2   8.99    1.798      88.102           No           Paid          2007
3  89.00    0.000     178.000           No           Paid          2010
4 189.00    0.000     189.000           No           Paid          2013
5  36.99    7.398     362.502           No           Paid          2015

Data Cleaning

The data has been thoroughly verified to ensure there are no missing values.

[1] FALSE
[1] 0

Data Transformation

During data transformation the column name has been standardized.

 [1] "EmpID"              "Employee.Name"      "Employee.Job.Title"
 [4] "Sales.Region"       "OrderNum"           "OrderDate"         
 [7] "OrderType"          "CustomerType"       "CustName"          
[10] "CustState"          "ProdCategory"       "ProdNumber"        
[13] "ProdName"           "Quantity"           "Price"             
[16] "Discount"           "Order.Total"        "Payment.Plan"      
[19] "Payment.Status"     "DateCustAdded"     

Exploratory Data Analysis (EDA)

For exploratory data analysis, summary statistics were generated for selected key columns. Additionally, visualizations were created, including a bar plot to represent regions distribution and a histogram to illustrate the distribution of quantity.

    Quantity          Price           Discount        Order.Total      
 Min.   :  1.00   Min.   :  4.99   Min.   :  0.000   Min.   :    4.99  
 1st Qu.:  1.00   1st Qu.: 14.99   1st Qu.:  0.000   1st Qu.:   42.99  
 Median :  5.00   Median : 31.47   Median :  2.198   Median :  179.04  
 Mean   : 11.11   Mean   :126.85   Mean   : 14.369   Mean   : 1386.50  
 3rd Qu.: 10.00   3rd Qu.:179.00   3rd Qu.:  8.598   3rd Qu.:  691.20  
 Max.   :109.00   Max.   :899.00   Max.   :179.800   Max.   :83708.40  


Integration with Python

We make sure the connection and data loaded to avoid any error and furthermore to demonstrate the integration of Python within the Quarto document, we will generate a Pareto chart for sales by product category and a Sunburst chart visualizing customer distribution across Customer Type, Sales Region, and Customer State which we later can see in the chapter 3. Results.

Available tables:     name
0  sales
✅ Sales data loaded successfully!

3. Results

3.1 Sales Trend YoY Analysis

3.2 Sales Trend By Region Analysis

3.3 Top-selling Products, Categories & Customers

ProdName Top 5
RWW-75 Robot 653773.2
MICR-23K Robot 535084.8
DTD-7000 Drone 447210.0
DX-145 Drone 381550.0
MICR-564K Drone 364669.2
ProdCategory Top 5
Robots 2469966.0
Drones 1820585.2
Robot Kits 1152879.0
Drone Kits 682597.1
Training Videos 416812.7
CustName Top 5
Boehm Inc. 155815.75
Wintheiser Inc. 109909.27
Schuppe Inc. 103297.33
Cremin LLC 92517.26
Streich LLC 89598.67
([0, 1, 2, 3, 4, 5, 6], [Text(0, 0, 'Robots'), Text(1, 0, 'Drones'), Text(2, 0, 'Robot Kits'), Text(3, 0, 'Drone Kits'), Text(4, 0, 'Training Videos'), Text(5, 0, 'eBooks'), Text(6, 0, 'Blueprints')])

3.4 Customer Segmentation

3.5 Scatter Plot Price vs Quantity


4. Conclusion

4.1 Summary of Key Findings

  • Sales Trends YoY: The overall sales trend shows significant revenue growth from 2017 to 2018, followed by a decline in 2019. The sharp increase in 2018 suggests strong business performance, likely due to improved marketing, higher customer acquisition, or an increase in product demand. However, the decline in 2019 indicates potential challenges such as market saturation, increased competition, or ineffective pricing strategies. Further analysis is required to determine whether external factors or internal business decisions contributed to this drop. Furthermore we analyse the sales by region we notice that most regions experienced a decline in 2019 particularly S Cental West and Southwest while N Central West and Northwest consistently reported lower sales across all year suggesting a need for targeted business strategies
  • Pareto Chart: The “Robots”category contributes the highest revenue, followed by “Drone”, “Robot Kits”, and “Drone Kits”. These four category combine contributes to 88.8% of total sales.
  • Customer Segmentation: The majority of total spending comes from Business customers, which accounts for 6,577K in total sales in whic 50% out of it contributed by Central East, North East and N Central East. Individual customers contribute significantly less, totaling 322K in sales. This suggest that the company primarily serves business clients aligning with the company’s target market.
  • Price vs Quantity: The Scatter plot shows relationship between Price and Quantity which most of sales transactions occur at lower price points, with a high concentration of data points below 250 in price. As price increase the quantity sold decrease indicating price sensitivity.

4.2 Actionable Recommendations

Based on the findings from the analysis, the following recommendations can be implemented to address the identified challenges and optimize business performance:

Sales Growth Strategies

  1. Investigate the 2019 Sales Decline:

    • Conduct a deeper analysis of external factors (e.g., economic downturn, competitor strategies) and internal factors (e.g., pricing strategy, marketing effectiveness) that may have contributed to the revenue drop.

    • Implement corrective measures such as revisiting pricing models or introducing customer retention initiatives.

  2. Enhance Sales Strategies in Declining Regions:

    • S Central West and Southwest saw significant declines; targeted marketing campaigns, localized promotions, and better distribution strategies can help recover lost sales.

    • For consistently lower-performing regions (N Central West, Northwest), consider customer segmentation analysis to better understand demand and introduce region-specific product offerings or incentives.

  3. Leverage High-Performing Regions for Growth:

    • Central East, Northeast, and N Central East are strong contributors to business sales. Capitalizing on these regions with increased marketing efforts and exclusive business deals could help sustain growth.

    • Expand strategic partnerships with major business clients in these regions to maintain long-term customer retention.

Product & Inventory Optimization

  1. Prioritize High-Contributing Categories:

    • The Pareto analysis shows that “Robots,” “Drones,” “Robot Kits,” and “Drone Kits” account for 88.8% of total sales. Ensuring sufficient inventory levels, streamlining supply chain logistics, and implementing targeted promotions will help sustain and boost revenue from these products.

    • Introduce up-selling or bundling strategies for top-selling products to increase revenue per transaction.

  2. Expand Product Offerings in Lower-Contributing Categories:

    • The eBooks and Blueprints categories contribute the least to revenue. Consider evaluating demand for these products, improving their value proposition, or repackaging them with high-performing categories for better market adoption.

Customer & Pricing Strategies

  1. Maximize Business Customer Engagement:

    • With Business clients accounting for 6,577K in total sales, consider exclusive loyalty programs, bulk purchase discounts, and personalized account management to drive repeat business.

    • Strengthen relationships with existing high-value business customers through tailored services and long-term agreements.

  2. Improve Individual Customer Engagement:

    • While individual customers contribute less (322K in sales), this segment presents a potential growth opportunity.

    • Consider offering special incentives, promotional discounts, or subscription-based models to encourage higher spending from individuals.

    • Create basic, premium, and enterprise-tier plans to cater to different customer segments.

  3. Optimize Pricing Strategies to Address Price Sensitivity:

    • The Price vs Quantity scatter plot indicates that lower-priced products dominate sales, and higher prices reduce quantity sold.

    • Implement dynamic pricing models, offering volume-based discounts or targeted promotions on high-priced items to encourage larger purchases.

4.3 Limitations & Future Steps

Limitations of the Analysis

  1. Lack of External Market Data:

    • The analysis primarily focuses on internal sales data, without considering macro-economic trends, competitor strategies, or industry-wide demand fluctuations that could impact sales performance.

    • Future research should incorporate market intelligence reports, competitor pricing analysis, and economic indicators for a more comprehensive business strategy.

  2. Seasonality and Demand Fluctuations Not Analyzed:

    • Sales seasonality (e.g., holiday sales, promotional periods) was not accounted for in this analysis, which could provide valuable insights into peak and low-demand periods.

    • Future work should include time series forecasting models to detect seasonality and optimize inventory planning and marketing campaigns.

  3. Customer Behavior Insights Limited:

    • The study highlights the distribution of customer spending (Business vs. Individual customers) but does not provide deep insights into purchase behavior, preferences, or retention rates.

    • Future analysis could explore customer lifetime value (CLV), churn prediction models, and sentiment analysis from customer feedback.

  4. Limited Granularity on Product-Level Sales Performance:

    • While the Pareto chart identifies high-revenue product categories, a more detailed sub-category or SKU-level analysis could help refine inventory and pricing decisions further.

    • Future steps should include a drill-down analysis of sales trends for individual products within top-performing categories.

Future Steps

Implement Predictive Analytics for Forecasting

  • Utilize machine learning models to predict future sales trends based on historical data, seasonality, and external factors.

  • Incorporate AI-driven demand forecasting to optimize inventory and prevent stockouts or overstocking.

Expand Data Integration for Better Decision-Making

  • Integrate additional data sources, such as customer engagement metrics, industry benchmarks, and competitor pricing strategies, to refine strategic decisions.

  • Explore using real-time dashboards and automation tools to continuously track performance and respond dynamically to market changes.

Conduct A/B Testing for Pricing & Promotions

  • Test different discount strategies and promotional campaigns to assess the impact on sales and profitability.

  • Implement personalized pricing recommendations based on customer purchasing behavior.

Optimize the Sales Funnel for Individual Customers

  • Develop targeted marketing campaigns to increase engagement and conversion rates for individual customers.

  • Introduce subscription models or premium-tier offers to boost long-term value from individual consumers.